﻿using Microsoft.Data.SqlClient;
using Newtonsoft.Json.Linq;
using Quartz.Impl.AdoJobStore.Common;
using System.Text;

namespace XJay.Scheduler.Repositories
{
    public class RepositorieSqlServer : IRepositorie
    {
        private IDbProvider DBProvider { get; }
        public RepositorieSqlServer(IDbProvider dbProvider)
        {
            DBProvider = dbProvider;
        }

        public async Task<int> InitTable()
        {
            using (var connection = new SqlConnection(DBProvider.ConnectionString))
            {
                await connection.OpenAsync();
                var check_sql = @"SELECT
	                                    COUNT (1)
                                    FROM
	                                    sys.tables
                                    WHERE
	                                    name IN (
		                                    'QRTZ_BLOB_TRIGGERS',
		                                    'QRTZ_CALENDARS',
		                                    'QRTZ_CRON_TRIGGERS',
		                                    'QRTZ_FIRED_TRIGGERS',
		                                    'QRTZ_JOB_DETAILS',
		                                    'QRTZ_LOCKS',
		                                    'QRTZ_PAUSED_TRIGGER_GRPS',
		                                    'QRTZ_SCHEDULER_STATE',
		                                    'QRTZ_SIMPLE_TRIGGERS',
		                                    'QRTZ_SIMPROP_TRIGGERS',
		                                    'QRTZ_TRIGGERS'
	                                    );";
                SqlCommand sqlCommand = new SqlCommand(check_sql,connection);
                int count = (int) await sqlCommand.ExecuteScalarAsync();
                //初始化 建表
                if (count == 0)
                {
                    string init_sql = await File.ReadAllTextAsync("Tables/tables_sqlServer.sql");
                    sqlCommand = new SqlCommand(init_sql, connection);
                    return await sqlCommand.ExecuteNonQueryAsync();
                }
                await connection.CloseAsync();
            }
            return 0;
        }

        public async Task<bool> RemoveErrLogAsync(string jobGroup, string jobName)
        {
            try
            {
                using (var connection = new SqlConnection(DBProvider.ConnectionString))
                {
                    await connection.OpenAsync();
                    string sql = $@"SELECT
	                                JOB_DATA
                                FROM
	                                QRTZ_JOB_DETAILS
                                WHERE
	                                JOB_NAME = @jobName
                                AND JOB_GROUP = @jobGroup";

                    SqlCommand sqlCommand = new SqlCommand(sql, connection);
                    sqlCommand.Parameters.Add(new SqlParameter
                    {
                        ParameterName = "@jobGroup",
                        Value = jobGroup,
                    });
                    sqlCommand.Parameters.Add(new SqlParameter
                    {
                        ParameterName = "@jobName",
                        Value = jobName,
                    });
                    var byteArray = (byte[]) await sqlCommand.ExecuteScalarAsync();
                    var jsonStr = Encoding.Default.GetString(byteArray);
                    JObject source = JObject.Parse(jsonStr);
                    source.Remove("Exception");//移除异常日志 
                    var modifySql = $@"UPDATE QRTZ_JOB_DETAILS
                                    SET JOB_DATA = @jobData
                                    WHERE
	                                    JOB_NAME = @jobName
                                    AND JOB_GROUP = @jobGroup";
                    sqlCommand.Parameters.Add(new SqlParameter
                    {
                        ParameterName = "@jobData",
                        Value = Encoding.Default.GetBytes(source.ToString()),
                        DbType = System.Data.DbType.Byte
                    });
                    sqlCommand.CommandText = modifySql;
                    await sqlCommand.ExecuteNonQueryAsync();
                    await connection.CloseAsync();
                }
                
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}
